﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ABII.Entities;
using System.Data.Objects;
using System.Xml.Linq;
using System.IO;

namespace ABII.AdminPanel
{
    public static class Queries
    {
        private static MessengerEntities entities = new MessengerEntities();

        public static Int32 Authenticate(String username, String password)
        {
            List<Int32> list = new List<Int32>(from rank in entities.Authenticate(username, password)
                                             select rank.ID);
            return (list.Count > 0) ? list[0] : 0;
        }

        public static void ExportData(String path)
        {
            XElement account = GetAccountExportData();
            XElement ban = GetBanExportData();
            new XElement("Database", 
                GetAccountExportData(), 
                GetBanExportData(), 
                GetSilenceExportData(), 
                GetApplicationExportData()
            ).Save(path);
        }

        public static XElement GetAccountExportData()
        {
            var xml = (from account in entities.Account
                      select new { Element = "Account",
                        ID = account.ID,
                        Username = account.Username, 
                        Password = account.Password }).ToList();
            XElement[] accounts = new XElement[xml.Count];
            for (Int32 index = 0; index < xml.Count; ++index)
            {
                var element = xml[index];
                accounts[index] = new XElement(element.Element,  
                                    new XAttribute("ID", element.ID),
                                    new XAttribute("Username", element.Username),
                                    new XAttribute("Password", element.Password));
            }
            return new XElement("Accounts", accounts);
        }

        public static XElement GetBanExportData()
        {
            var xml = (from ban in entities.Ban
                      select new { Element = "Ban",
                        ID = ban.AccountID,
                        Expire = ban.Expire, 
                        Reason = ban.Reason, 
                        AdminID = ban.AdminID }).ToList();
            XElement[] bans = new XElement[xml.Count];
            for (Int32 index = 0; index < xml.Count; ++index)
            {
                var element = xml[index];
                bans[index] = new XElement(element.Element, 
                                    new XAttribute("ID", element.ID),
                                    new XAttribute("Expire", element.Expire),
                                    new XAttribute("Reason", element.Reason),
                                    new XAttribute("AdminID", element.AdminID));
            }
            return new XElement("Bans", bans);
        }

        public static XElement GetSilenceExportData()
        {
            var xml = (from ban in entities.Silence
                      select new { Element = "Silence",
                        ID = ban.AccountID,
                        Expire = ban.Expire, 
                        Reason = ban.Reason, 
                        AdminID = ban.AdminID }).ToList();
            XElement[] silences = new XElement[xml.Count];
            for (Int32 index = 0; index < xml.Count; ++index)
            {
                var element = xml[index];
                silences[index] = new XElement(element.Element, 
                                    new XAttribute("ID", element.ID),
                                    new XAttribute("Expire", element.Expire),
                                    new XAttribute("Reason", element.Reason),
                                    new XAttribute("AdminID", element.AdminID));
            }
            return new XElement("Silences", silences);
        }

        public static XElement GetApplicationExportData()
        {
            var xml = (from log in entities.ApplicationLog
                      select new { Element = "ApplicationLog",
                        ID = log.AccountID,
                        AdminID = log.AdminID, 
                        Date = log.Date, 
                        Changelog = log.Changelog }).ToList();
            XElement[] logs = new XElement[xml.Count];
            for (Int32 index = 0; index < xml.Count; ++index)
            {
                var element = xml[index];
                logs[index] = new XElement(element.Element, 
                                    new XAttribute("ID", element.ID),
                                    new XAttribute("AdminID", element.AdminID),
                                    new XAttribute("Date", element.Date),
                                    new XAttribute("Changelog", element.Changelog));
            }
            return new XElement("ApplicationLogs", logs);
        }

        public static SecurityData GetSecurityData(String username, PERMISSION permission)
        {
            SecurityData data = (from record in entities.Account
                                    .Include("Private")
                                    .Include("Rank")
                                    .Include("Gender")
                                    where record.Username == username
                                    select new SecurityData
                                    {
                                        Username = record.Username, 
                                        Password = record.Password, 
                                        Rank = record.Private.Rank.Name, 
                                        Gender = record.Private.Gender.Name, 
                                        Birthday = record.Private.Birthday, 
                                        Question = record.Private.SecurityQuestion, 
                                        Answer = record.Private.SecurityAnswer
                                    }).First();
            data.Permission = permission;
            return data;
        }

        public static PrivateData GetPrivateData(String username, PERMISSION permission)
        {
            Byte[] buffer = (from record in entities.Account
                                .Include("Avatar")
                                where record.Username == username
                                select record.Avatar.Data
                             ).First();
            PrivateData data = new PrivateData()
            {
                Avatar = (buffer != null) ? new Avatar(new MemoryStream(buffer)) : new Avatar(),
                Permission = permission
            };
            data.Friends = new List<Record>();
            foreach (Rank rank in entities.GetFriends(username))
            {
                data.Friends.Add(new Record(rank.ID, rank.Name));
            }
            return data;
        }

        public static void SaveSecurityData(String username, SecurityData data, Int32 rankID, Int32 genderID)
        {
            var query = (from record in entities.Account
                         .Include("Private.Rank")
                         .Include("Private.Gender")
                        where record.Username == username
                        select record).First();
            query.Username = data.Username;
            query.Private.Rank = (from rank in entities.Rank
                                 where rank.Name == data.Rank
                                 select rank).First();
            query.Private.Gender = (from gender in entities.Gender
                                     where gender.Name == data.Gender
                                     select gender).First();
            query.Private.SecurityQuestion = data.Question;
            query.Private.SecurityAnswer = data.Answer;
            query.Private.Birthday = data.Birthday;
            entities.SaveChanges();
            if (data.Password.Length > 0)
            {
                entities.SetPassword(data.Username, data.Password).First();
            }
        }

        public static List<Record> GetRelations()
        {
            List<Record> list = new List<Record>();
            foreach (Relation rank in (from relation in entities.Relation
                                   select relation).ToList())
            {
                list.Add(new Record(rank.ID, rank.Name));
            }
            return list;
        }

        public static List<Record> GetRanks()
        {
            List<Record> list = new List<Record>();
            foreach (Rank rank in (from rank in entities.Rank
                                   select rank).ToList())
            {
                list.Add(new Record(rank.ID, rank.Name));
            }
            return list;
        }

        public static List<Record> GetGenders()
        {
            List<Record> list = new List<Record>();
            foreach (Gender gender in (from gender in entities.Gender
                                        select gender).ToList())
            {
                list.Add(new Record(gender.ID, gender.Name));
            }
            return list;
        }

        public static List<Record> GetStates()
        {
            List<Record> list = new List<Record>();
            foreach (State state in (from state in entities.State
                                        select state).ToList())
            {
                list.Add(new Record(state.ID, state.Name));
            }
            return list;
        }

        public static List<Record> GetUsers()
        {
            List<Record> list = new List<Record>();
            var query = from user in entities.Account
                            .Include("Private")
                            .Include("Rank")
                        select new
                        {
                            ID = user.Private.Rank.ID,
                            Username = user.Username
                        };
            foreach (var record in query)
            {
                list.Add(new Record(record.ID, record.Username));
            }
            return list;
        }

        public static Int32 GetFeedbackCount()
        {
            return (from c in entities.Feedback
                    select new { c.AccountID }).Count();
        }

        public static void AddBlackList(String address, String reason)
        {
            Blacklist black = new Blacklist
            {
                IPAddress = address,
                Reason = reason
            };
            entities.AddToBlacklist(black);
            entities.SaveChanges();
        }

        public static void EditBlackList(String address, String reason)
        {
            Blacklist black = (from b in entities.Blacklist
                               where b.IPAddress == address
                               select b).First();
            black.Reason = reason;
            entities.SaveChanges();
        }

        public static void RemoveBlackList(String address)
        {
            Blacklist black = (from b in entities.Blacklist
                               where b.IPAddress == address
                               select b).First();
            entities.DeleteObject(black);
            entities.SaveChanges();
        }

        public static List<Report.Column> GetReportByRank(Int32 count)
        {
            List<Report.Column> list = new List<Report.Column>();
            var q = from rank in entities.Rank
                    join p in entities.Private on rank.ID equals p.Rank.ID
                    group rank by rank.ID into grouped
                    select new 
                    { 
                        Count = grouped.Count(),
                        Name = grouped.FirstOrDefault().Name
                    };
            foreach (var row in q)
            {
                Int32 number = Convert.ToInt32(row.Count);
                list.Add(new Report.Column(row.Name.ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            
            return list;
        }

        public static List<Report.Column> GetReportByGender(Int32 count)
        {
            List<Report.Column> list = new List<Report.Column>();
            var q = from gender in entities.Gender
                    join p in entities.Private on gender.ID equals p.Gender.ID
                    group gender by gender.ID into grouped
                    select new 
                    { 
                        Count = grouped.Count(),
                        Name = grouped.FirstOrDefault().Name
                    };
            foreach (var row in q)
            {
                Int32 number = Convert.ToInt32(row.Count);
                list.Add(new Report.Column(row.Name.ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            
            return list;
        }

        public static List<Report.Column> GetReportByState(Int32 count)
        {
            List<Report.Column> list = new List<Report.Column>();
            var q = from state in entities.State
                    join p in entities.General on state.ID equals p.State.ID
                    group state by state.ID into grouped
                    select new 
                    { 
                        Count = grouped.Count(),
                        Name = grouped.FirstOrDefault().Name
                    };
            foreach (var row in q)
            {
                Int32 number = Convert.ToInt32(row.Count);
                list.Add(new Report.Column(row.Name.ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
            }
            
            return list;
        }
        //private static List<Report.Column> GetReportByBan(Int32 count)
        //{
        //    List<Report.Column> list = new List<Report.Column>();
        //    //orderbyban
        //    foreach (DataRow row in dataSet.Tables["Admin"].Rows)
        //    {
        //        Int32 number = Convert.ToInt32(row["Count"]);
        //        list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
        //    }
        //    return list;
        //}
        //private static List<Report.Column> GetReportBySilence(Int32 count)
        //{
        //    List<Report.Column> list = new List<Report.Column>();
        //  //reportbysilence

        //    foreach (DataRow row in dataSet.Tables["Admin"].Rows)
        //    {
        //        Int32 number = Convert.ToInt32(row["Count"]);
        //        list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " member" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
        //    }
        //    return list;
        //}
        //private static List<Report.Column> GetReportByRelation(Int32 count)
        //{
        //    List<Report.Column> list = new List<Report.Column>();
        //    //reportbyrelation
        //    foreach (DataRow row in dataSet.Tables["Relation"].Rows)
        //    {
        //        Int32 number = Convert.ToInt32(row["Count"]);
        //        list.Add(new Report.Column(row["Username"].ToString(), number.ToString() + " active relation" + ((number > 1) ? "s" : " ") + " (" + number * 100.0f / count + "%)"));
        //    }
        //    return list;
        //}
        private static List<Report.Column> GetReportByFeedback()
        {
            List<Report.Column> list = new List<Report.Column>();
            var report = from a in entities.Account
                         join f in entities.Feedback on a.ID equals f.AccountID //into temp
                         select new
                         {
                             Username = a.Username,
                             Feed = f.Feedback1
                         };


            foreach (var row in report)
            {
                list.Add(new Report.Column(row.Username.ToString(), row.Feed.ToString()));
            }
            return list;
        }

        public static void GetFeedback()
        {
            List<Report.Title> title = new List<Report.Title>();
            title.Add(new Report.Title("Feedbacks", GetReportByFeedback()));
            Report report = new Report(title);
        }

        private static List<Report.Column> GetReportByBlacklist()
        {
            List<Report.Column> list = new List<Report.Column>();
            foreach (Blacklist blc in (from blc in entities.Blacklist
                                           select blc).ToList())
            {
                list.Add(new Report.Column(blc.IPAddress, blc.Reason));
            }
            return list;
        }

        public static void Dispose()
        {
            //entities.Dispose();
        }
    }
}
